<?php
namespace hyang;
use PDO;// 命名空间影响系统原有类的调用
use PDOException;
use Exception;

/*  
 *  数据库驱动
 *  初始化参数：  [
 *     "user"=>"用户","password"=>"密码","dbname"=>"数据库名称" 必须
 *      "dsn"=>"数据库驱动" 或者 "type"=>"数据库类型-默认MYSQL","host"=>"域名-默认localhost",
 * ]
 *  ORACLE host => ip:port/ 端口号默认1521
 *  DB类不支持上下文操作(底层数据库操作的改进)，而Model类支持(高级化数据处理)
 */
class Db{
    // 构造函数-对象初始化
    public $db;         //数据库对象
    public $debuging;   // 开启调试模式
    public $error;      //数据库连接的错误信息    
    public $type;       //数据库类型
    public $option;     // 数据库选项值
    public $sql;        //执行SQL语句
    private $_kSep;     //数据 key标点符
    private $_vSep;     //数据 value标点符
    
    public function __construct($opt=null)
    {
        $this->debuging = (defined('Pattern') && Pattern == 'PRODUCTION')? false:true;
        if(empty($opt)){
            $opt = config('database');            
        }
        if(!isset($opt['user']) || !isset($opt['password'])){
            if($this->debuging) $this->_log('数据库所需的参数不足够，user/password！');
            return;
        }
        $this->option = $opt;
        $type = isset($opt['type'])? strtoupper($opt['type']):"MYSQL";
        $host = isset($opt['host'])? $opt['host']:'localhost';
        $dbname = isset($opt['dbname'])? $opt['dbname']:'hyang';
        $charset = isset($opt['charset'])? $opt['charset']:'utf8';
        $dsn = null;

        $this->type = $type;
        if(isset($opt['dsn'])) $dsn = $opt['dsn'];
        else{             
            // 匹配数据库类型
            switch ($type){
                case "MYSQL"://mysql:host=localhost;dbname=testdb / mysql:dbname=testdb;host=127.0.0.1
                    $dsn = 'mysql:dbname='.$dbname.';host='.$host.';charset='.$charset;
                    $this->_kSep = '`';$this->_vSep = '"';
                    break;
                case "ORACLE"://oci:dbname=//localhost:1521/mydb
                    if(isset($opt['port'])) $host .= ':'.$opt['port'];
                    elseif(substr_count($host,':') == 0) $host .= ':1521';
                    $dsn = 'oci:dbname=//'.$host.'/'.$dbname.';charset='.$charset;//utf8而非utf-8
                    $this->_kSep = '"';$this->_vSep = '\'';
                    break;
                case "PGSQL":
                    // pgsql:host=localhost;port=5432;dbname=testdb;user=bruce;password=mypass
                    // pgsql:dbname=$dbname;host=$host
                    if(isset($opt['port'])) $host .= ':'.$opt['port'];
                    elseif(substr_count($host,':') == 0) $host .= ':5432';
                    $dsn = 'pgsql:dbname='.$dbname.';host='.$host;
                    $dsn = 'pgsql:dbname='.$dbname.';host=localhost';                    
                    $this->_kSep = '"';$this->_vSep = '\'';
                    break;
            }                       
        }
        if(empty($dsn)){
            if($this->debuging) $this->_log('数据库所需的参数不足够,dsn值的为空！');
            return;
        }
        try{
            $this->db = new PDO($dsn,$opt['user'],$opt['password']);
            // 数据库预处理
            switch ($type){
                case "MYSQL":
                    break;
                case "ORACLE":
                    $this->db->query('ALTER SESSION SET NLS_DATE_FORMAT=\'yyyy-mm-dd hh24:mi:ss\'');//设置日期格式
                    break;
            }
        }catch(PDOException $e){
            if($this->debuging) $this->_log('数据库连接失败：'.$e->getMessage());
        }
    }
    // 新建数据表 $param = ['table'=>数据表,'value'=>['name'=>'']-列表选项]/['table'=>'数据表','sql'=>'创建sql']
    public function create($param,$build=false)
    {
        if(is_array($param)){
            $sql = '';
            $sep = $this->_kSep;
            if(isset($param['table'])){
                $sql = 'create table '.$sep.$param['table'].$sep.'(';
                if($this->table_exist($param['table'])){
                    $this->_error($param['table'].'表已经存在，无法创建已经存在的数据表！');
                    return false;
                }                
            }
            if(isset($param['sql'])) $sql = $param['sql'];
            elseif(isset($param['value'])){
                $param['value'] = is_array($param['value'])? $param['value']:array();
                $len = count($param['value']);$i = 0;
                foreach($param['value'] as $k=>$v){
                    $sql .= $sep.$k.$sep.' '.$v;$i++;
                    if($len != $i) $sql .= ',';
                    
                }
                $sql .= ')';
            }
        }else $sql = $param;
        if($build) return $sql;
        try{$this->db->exec($sql);return true;}
        catch(Exception $e){$this->_log($e->getMessage());return false;}
    }
    // 数据库删除对象 $param = string/array=> $sql/[type=>"table,默认为删除数据表","table"=>"表名"]
    public function drop($param,$build=false)
    {
        $sql = '';
        if(is_string($param)) $sql = $param;
        elseif(is_array($param) && isset($param['table'])){
            $type = isset($param['type'])? $param['type']:'table';
            $sql = 'drop '.$type.' '.$this->cname($param['table']);
        }
        if(empty($sql)) return false;
        $this->_sql($sql);
        if($build) return $sql;
        else{
            try{$this->db->exec($sql);return true;}
            catch(Exception $e){$this->_log($e->getMessage());return false;}
        }
    }
    // 数据新增
    public function add($tb,$data=null)
    {
        if($data) $sql = $this->parseInsert($data,$tb);
        else $sql = $tb;
        $this->_sql($sql);
        if(empty($sql)) return 0;
        return $this->db->exec($sql);
    }    
    // 获取新增数据的ID
    public function getAddId(){
        return $this->db->lastInsertId();
    }
    // 删除数据
    public function delete($tb,$where=null)
    {
        $sql = 'delete from '.$this->cname($tb);
        $wh = $this->parseWhere($where);
        if($wh) $sql .= ' where '.$wh;
        $this->_sql($sql);
        return $this->db->exec($sql);
    }
    // 更改数据
    public function update($tb,$data,$where=null,$bind=true)
    {
        // update table set c1='',c2='' where ...
        $sql = 'update '.$this->cname($tb).' set ';
        $sql .= $this->parseUpdate($data);
        $isBind = is_array($where) && $bind? true:false;//debug([$bind,is_array($where),$where],true);
        $wh = $this->parseWhere($where,$isBind);
        if($wh) $sql .= ' where '.$wh;
        $this->_sql($sql);
        if($isBind){
            $sth = $this->db->prepare($sql);
            return $sth->execute($where);
        }
        else return $this->db->exec($sql);
    }
    // 普通sql查询-可绑定参数
    public function query($sql,$bind=null)
    {
        if(!is_object($this->db)){
			return null;
		}
        $this->_sql($sql);
        // 支持数据绑定
        if(is_array($bind)){
            $sth = $this->db->prepare($sql);
            $sth->execute($bind);       
        }else $sth = $this->db->query($sql);             
        $data = $sth->fetchAll(PDO::FETCH_ASSOC);//PDO::FETCH_ASSOC 设置为key=>value
        // pgsql 无法使用 fetchAll 方法
		return $data;
    }
    // 获取单列数据
    public function fetchRow($sql,$bind=null)
    {
        if(!is_object($this->db)){
			return null;
		}
        $this->_sql($sql);
        // 支持数据绑定
        if(is_array($bind)){
            $sth = $this->db->prepare($sql);
            $sth->execute($bind);
        }else $sth = $this->db->query($sql);//debug($sql);
        if(!is_object($sth)) return '';
		$data = $sth->fetch(PDO::FETCH_ASSOC);
		return $data;
    }
    // 获取第一个数据字段
    public function fetchOne($sql,$bind=null)
    {
        $this->_sql($sql);
        $data = $this->fetchRow($sql,$bind);
        if(is_array($data)){
            $data = array_values($data);
            return isset($data[0])? $data[0]:'';
        }
        return '';
    }
    // 数据简化查询-$tb 可作为回调函数用户Oracle的分页
    public function select($tb,$where=null,$appendSql=null,$field=null,$bind=null)
    {
        // 支持回调函数-用户对oracle数据库分页支持
        $isPageing = false;$pageOpt = null;
        if(is_array($tb)){
            if(isset($tb[1]) && $tb[1] == 'Pager'){
                if(isset($tb[2]) && is_array($tb[2])){
                    $pageOpt = $tb[2];
                    $isPageing = true;
                }
            }
            $tb = $tb[0];
        }
        $sep = $this->_kSep;
        $sql = '';
        if(empty($where) && empty($field)) $sql = 'select * from '.$sep.$tb.$sep;
        else{
            $sql = 'select'.$this->field($field).'from '.$sep.$tb.$sep;
            $isBind = false;
            if(is_string($where)) $wh = $where;
            else{
                if(is_array($bind) && is_array($where)) $isBind = true;
                $wh = $this->parseWhere($where,$isBind);
            }
            if($wh) $sql .= ' where '.$wh;
        }
        if($appendSql) $sql .= ' '.$appendSql;
        $this->_sql($sql);
        if($isPageing){
            $sql = $this->page($pageOpt,$sql);
        }
        return $this->query($sql,$bind);
    }
    // 通过ID查询数据(数据简化查询) $field[array/string]
    public function selectById($tb,$where,$field=null)
    {
        $sql = 'select';
        if(is_array($field)){
            $sql .= $this->field($field);
        }
        elseif($field) $sql .= $field;
        else $sql .= ' * ';
        $sql .= 'from '.$this->cname($tb).' where ';
        if(is_array($where)){// 解析数据绑定
            $sep = $this->_kSep;
            $tmp = [];
            foreach($where as $k=>$v){
                $tmp[] = $sep.$k.$sep.'=:'.$k;
            }
            $sql .= implode(',',$tmp);
            return $this->fetchRow($sql,$where);
        }
        $sql .= $where;
        $this->_sql($sql);
        return $this->fetchRow($sql);
    }
    // 聚合函数调用-支持简单 count/sum/avg/max/min
    public function aggregate($tb,$type,$field=null,$where=null)
    {
        $sql = 'select '.$type.'('.($field? $this->cname($field):'*').') from '.$this->cname($tb);
        if($where) $sql .= ' where '.$this->parseWhere($where);
        return $this->fetchOne($sql);
    }
    //数据表是否存在
    public function table_exist($tb)
    {
        switch ($this->type){
            case 'MYSQL'://从0开始（注意不是1），第二个参数指定最多返回行数
                $sql = 'show tables like "'.$tb.'"';$this->_sql($sql);
                $ct = $this->fetchOne($sql);
                if($ct) return true;
                break;
            case 'ORACLE':
                $sql = 'select count(1) from user_tables where table_name=\''.$tb.'\'';
                $ct = $this->fetchOne($sql);$this->_sql($sql);
                if($ct > 0) return true;                
                break;
            case 'PGSQL':
                $sql = 'select count(1) from pg_class where relnamespace=\'2200\' and relname=\''.$tb.'\'';
                $ct = $this->fetchOne($sql);$this->_sql($sql);
                if($ct > 0) return true;
                break;
        }
        return false;
    }
    /************** 数据库辅助工具 ******************************/
    // 条件解析- 数据绑定
    public function parseWhere($param,$isBind=false)
    {
        $where = '';
        if(empty($param)) return $where;
        // $sep 变量名符合， $sep2 参数字符
        $sep = $this->_kSep;
        $sep2 = $this->_vSep;
        if(is_array($param)){
            $tmp = [];
            foreach($param as $k=>$v){
                if($isBind) $tmp[] = $sep.$k.$sep.'='.(is_bool($isBind) ? ':'.$k:$isBind);
                else $tmp[] = $sep.$k.$sep.'='.$sep2.$v.$sep2;
            }
            if($tmp) $where = implode(' and ',$tmp);
        }
        elseif($param) $where = $param;
        return $where;
    }
    // parseInsert 解析新增数据
    public function parseInsert($data,$tb)
    {
        if(!is_array($data)) return '';
        $sql = '';
        if($tb) $sql = 'insert into '.$this->cname($tb);
        $sep = $this->_kSep;
        $sep2 = $this->_vSep;
        $cols = array_keys($data);
        $value = array_values($data);
        $sql .= '('.$sep.implode($sep.','.$sep,$cols).$sep.') values('.$sep2.implode($sep2.','.$sep2,$value).$sep2.')';
        return $sql;
    }
    // 解析更改数据
    public function parseUpdate($data){
        if(is_string($data)) return $data;
        elseif(is_array($data)){
            $tmp = [];
            $sep = $this->_kSep;
            $sep2 = $this->_vSep;
            foreach($data as $k=>$v){
                $tmp[] = $sep.$k.$sep.'='.$sep2.$v.$sep2;
            }
            return implode(',',$tmp);
        }
        return '';
    }
    // 数据字段
    public function field($param){
        $sep = $this->_kSep;
        if(is_array($param)){            
            $glue = $sep.','.$sep;
            $text = ' '.$sep.implode($glue,$param).$sep.' ';
        }
        elseif($param && '*' != trim($param)) $text = $sep.$param.$sep;
        else $text = ' * ';
        return $text;// 反馈字符串参数
    }
    // 数据库字段处理
    public function cname($param){
        $sep = $this->_kSep;
        if(is_array($param)) $colname = $sep.implode($sep.','.$sep,$param).$sep;
        else $colname = $sep.$param.$sep;
        return $colname;
    }
    // 数据库分页- 数据分页选项/ 页码和显示数; $sql
    public function page($opt,$sql=''){
        $page = isset($opt['page']) && is_numeric($opt['page'])? intval($opt['page']):null;
        $num = isset($opt['num']) && is_numeric($opt['num'])? intval($opt['num']):null;
        if(empty($page) || empty($num)) return '';
        switch ($this->type){
            case 'MYSQL'://从0开始（注意不是1），第二个参数指定最多返回行数
                $page = ($page-1)*$num;
                $sql .= ' limit '.$page.','.$num;
                break;
            case 'ORACLE':
                $start = ($page-1).$num;$end = $start+$num-1;
                $sql = 'SELECT * FROM ( SELECT Ax.*, ROWNUM RN 
			        FROM ('.$sql.') Ax ) WHERE RN BETWEEN '.$start.' AND '.$end;
                break;
            case 'PGSQL':
                $page = ($page-1)*$num;
                $sql .= ' limit '.$num.' offset '.$page;
                break;
        }
        return $sql;
    }
    /************** 数据协助处理 ******************************/
    // 外壳接口实现
    private function _log($data,$feek=null){debug($data);}
    public function _sql($sql=null){
        if($sql){$this->sql = $sql;return true;}
        return $this->sql;
    }
    // 输出SQL运行的错误信息
    public function _error($data=null){
        if($data){$this->error = $data;return true;}
        return [
            'info' => $this->db->errorInfo(),
            'error'=> $this->error?$this->error:0,
            'sql'  => $this->sql
        ];
    }
}